 
 use newbiz
 go

truncate table users
 
go


insert into users(username, [UserPassword])
select 'admin','123'
go


truncate table member
go

insert into member([Id],memberID,[FirstName], [MiddleName], [LastName],[DateRegistered],[Category],[Agent],[MemberPlan])
select '1','4MAL000001','Ligaya','Gomez','Frades','9/19/2013','Client','','M-350'
union select '2','4MAL000002','Luningning','Paliza','Claveria','9/19/2013','Client','4MAL000001','M-350'
union select '3','6RSL000001','Olivia','Pignero','Costelo','9/10/2013','Agent','4MAL000001','M-350'
union select '4','6RSL000002','Delia','Isoy','Espino','9/13/2013','Client','4MAL000001','M-350'
union select '5','6RSL000003','Luz','Viaje','Manipol','9/19/2013','Client','ABAL000007','M-350'
union select '6','AAAA000001','Carmelita','Alvero','Maninang','10/16/2012','Client','AAAA000007','M-350'
union select '7','AAAA000002','Jocelyn','Noble','Geronimo','11/16/2012','Client','ABAL000001','M-350'
union select '8','AAAA000003','Felicidad','Caluya','Narcida','11/23/2012','Client','ABAL000001','M-350'
union select '9','AAAA000004','Merlyn','Palmero','Villaflor','12/8/2012','Client','AAAA000007','M-800'
union select '10','AAAA000005','Ariel','Morales','Gatchalian','12/17/2012','Client','AAAA000007','M-800'
union select '11','AAAA000006','Benjamin','Inocencio','Gravador','12/27/2012','Client','4MAL000001','M-800'
union select '12','AAAA000007','Noli','Macapinlac','Ocampo','1/30/2013','Client','4MAL000001','M-500'
union select '13','AAAA000008','Rommel','Begonia','Ruga','9/18/2012','Client','ABAL000007','M-500'
union select '14','AAAA000009','Ely','Inopia','Abanes','4/11/2013','Client','ABAL000007','M-500'
union select '15','AAAA000010','Aries','Maquincio','Espina','4/15/2013','Client','ABAL000007','M-1200'
union select '16','AAAA000011','Adonis','Arcillas','Villanueva','4/15/2013','Client','ABAL000007','M-1200'
union select '17','AAAA000012','Hilbert','David','Antipolo','5/14/2013','Client','AAAA000010','M-1200'
union select '18','AAAA000013','Enrico','Baldosa','Cadiente','6/17/2013','Client','ABAL000001','M-1200'
union select '19','AAAA000014','Rolando','Llavore','Barilla','6/26/2013','Client','ABAL000001','M-1200'
union select '20','AAAA000015','Virginia','Torres','Daniel','7/12/2013','Client','ABAL000001','M-1800'
union select '21','AAAA000016','Josephine','Oldan','Salcedo','7/24/2013','Client','ABAL000001','M-1800'
union select '22','AAAA000017','Alberto','Prado','Austria','8/9/2013','Client','AAAA000010','M-1200'
union select '23','AAAA000018','Genaro','A.','de Guzman','8/31/2013','Client','AAAA000010','M-1800'
union select '24','ABAL000001','Jennilyn','Reyes','Amatorio','5/4/2013','Agent','ABAL000003','M-1800'
union select '25','ABAL000002','Arnel','Sobremonte','Ariola','5/4/2013','Agent','AAAA000013','M-1200'
union select '26','ABAL000003','Alexander','Buencamino','Bihasa','5/4/2013','Agent','AAAA000013','M-1200'
union select '27','ABAL000004','Sherwin','Sibayan','Ufano','5/4/2013','Agent','AAAA000013' ,'M-1200'
union select '28','ABAL000005','Emilio','Salaum','Amatorio','5/4/2013','Agent','ABAL000003','M-1800'
union select '29','ABAL000006','Ralph Kevin','Angsanto','Reyes','5/4/2013','Agent','ABAL000003','M-1500'
union select '30','ABAL000007','Richard','Hermenegildo','Aguilar','5/4/2013','Agent','AAAA000006','M-1500'

go

select * from memberlevel

USE [NewBiz]
GO

truncate table [dbo].[MemberLevel]
GO

INSERT INTO [dbo].[MemberLevel]([MemberID] ,[AgentID],[MemberPlan] ,[Level] ,[CLevel]   ,[Amount]
           ,[DateRegistered],[DateProcess])
select [MemberID], agent, MemberPlan,1,1,100,getdate(), getdate() from member where isnull(agent,'') <> '' 
GO

select * from member

INSERT INTO [dbo].[MemberLevel]([MemberID] ,[AgentID],[MemberPlan] ,[Level] ,[CLevel]   ,[Amount]
           ,[DateRegistered],[DateProcess])
select [MemberID], agent, MemberPlan,1,1,100,getdate(), getdate() from member where isnull(agent,'') <> '' 
GO

INSERT INTO [dbo].[MemberLevel]([MemberID] ,[AgentID],[MemberPlan] ,[Level] ,[CLevel]   ,[Amount]
           ,[DateRegistered],[DateProcess])
select top 15 [MemberID], '4MAL000001', MemberPlan,2,2,40,getdate(), getdate() from member where isnull(agent,'') <> ''
GO

INSERT INTO [dbo].[MemberLevel]([MemberID] ,[AgentID],[MemberPlan] ,[Level] ,[CLevel]   ,[Amount]
           ,[DateRegistered],[DateProcess])
select top 15 [MemberID], 'AAAA000005', MemberPlan,2,2,40,getdate(), getdate() from member where isnull(agent,'') <> ''
GO

INSERT INTO [dbo].[MemberLevel]([MemberID] ,[AgentID],[MemberPlan] ,[Level] ,[CLevel]   ,[Amount]
           ,[DateRegistered],[DateProcess])
select top 15 [MemberID], 'ABAL000007', MemberPlan,2,0,0,getdate(), getdate() from member where isnull(agent,'') <> ''
and [MemberID] not in ('AAAA000005','4MAL000001')
 
GO
 
 

 INSERT INTO [dbo].[MemberLevel]([MemberID] ,[AgentID],[MemberPlan] ,[Level] ,[CLevel]   ,[Amount]
           ,[DateRegistered],[DateProcess])
select top 20 [MemberID], 'AAAA000018', MemberPlan,4,5,40,getdate(), getdate() from member where isnull(agent,'') <> ''
and [MemberID] not in ('AAAA000005','4MAL000001','AAAA000018','ABAL000007')
 
GO
  

   INSERT INTO [dbo].[MemberLevel]([MemberID] ,[AgentID],[MemberPlan] ,[Level] ,[CLevel]   ,[Amount]
           ,[DateRegistered],[DateProcess])
select top 20 [MemberID], 'ABAL000003', MemberPlan,5,6,40,getdate(), getdate() from member where isnull(agent,'') <> ''
and [MemberID] not in ('AAAA000005','4MAL000001','AAAA000018','ABAL000003')
 
GO
  
 INSERT INTO [dbo].[MemberLevel]([MemberID] ,[AgentID],[MemberPlan] ,[Level] ,[CLevel]   ,[Amount]
           ,[DateRegistered],[DateProcess])
select [MemberID], 'AAAA000018', MemberPlan,1,1,100,getdate()+15, getdate()+20 from member where isnull(agent,'') <> '' 
and [MemberID] not in ('AAAA000005','4MAL000001','AAAA000018','ABAL000003')
GO


 INSERT INTO [dbo].[MemberLevel]([MemberID] ,[AgentID],[MemberPlan] ,[Level] ,[CLevel]   ,[Amount]
           ,[DateRegistered],[DateProcess])
select [MemberID], 'ABAL000003', MemberPlan,1,1,100,getdate()+30, getdate()+40 from member where isnull(agent,'') <> '' 
and [MemberID] not in ('AAAA000005','4MAL000001','AAAA000018','ABAL000003')
GO

 INSERT INTO [dbo].[MemberLevel]([MemberID] ,[AgentID],[MemberPlan] ,[Level] ,[CLevel]   ,[Amount]
           ,[DateRegistered],[DateProcess])
select [MemberID], 'AAAA000005', MemberPlan,2,2,40,getdate()+30, getdate()+40 from member where isnull(agent,'') <> '' 
and [MemberID] not in ('AAAA000005','4MAL000001','AAAA000018','ABAL000003')
GO
 



update MemberLevel set CLevel = 0, Amount = 0 where Memberid in ('AAAA000011','AAAA000010')
and AgentID in ('AAAA000005')

go

update MemberLevel set CLevel = 0, Amount = 0 where Memberid in ('AAAA000005','AAAA000006')
and AgentID in ('4MAL000001')

go